import json
import os

def generate_insert_sql(file_path):
    # 从路径中提取 syllable 和 gender
    path_parts = file_path.split(os.sep)
    syllable = path_parts[-3]  # 假设 syllable 是倒数第三个部分
    gender = path_parts[-2]    # 假设 gender 是倒数第二个部分

    with open(file_path, 'r', encoding='utf-8') as file:
        json_data = json.load(file)
        data = json.dumps(json_data["data"])  # 将 JSON 数组转换为字符串
        
        # 检查 text 是否为列表，并适当地转义每个字符串元素
        if isinstance(json_data["text"], list):
            text = [s.replace("'", "''") for s in json_data["text"]]
            text = json.dumps(text)  # 将转义后的列表转换回 JSON 字符串
        else:
            text = json_data["text"].replace("'", "''")

        sql = f"INSERT INTO json_data (syllable, gender, data, text) VALUES ({syllable}, '{gender}', '{data}', '{text}');\n"
        return sql

def write_sql_to_file(output_file, base_path):
    with open(output_file, 'w', encoding='utf-8') as f:
        for root, dirs, files in os.walk(base_path):
            for file in files:
                if file.endswith('.json') and file != 'list.json':
                    file_path = os.path.join(root, file)
                    sql = generate_insert_sql(file_path)
                    f.write(sql)


# 示例用法
# file_paths = [r'.\output\5\female\He has a good job.json',  # 更多文件路径
#               r'.\output\5\female\list.json',
#               r'.\output\5\female\Drum and bugle corps.json'
#               ]  # 假设 list.json 也在这里
# output_file = 'output_sql.sql'  # 输出的 SQL 文件
# write_sql_to_file(output_file, file_paths)
base_path = r'D:\Doc\senior\flask_api\UC_Trains_Voice\output'  # 基本路径
output_file = 'output_sql.sql'  # 输出的 SQL 文件
write_sql_to_file(output_file, base_path)